﻿Imports System.Text
Imports System.IO
Imports System.Net
Imports System.Web
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Threading



Public Class Form1

    Public dataAdapter As SqlDataAdapter
    Public connection As SqlConnection


    Private Sub hotkeys(sender As Object, e As KeyEventArgs) Handles Me.KeyDown, conditionbox.KeyDown, error_list.KeyDown, enderecos_grade.KeyDown, nome_da_tabela_box.KeyDown
        If e.KeyCode = Keys.F1 Then pesquisadentro.Focus()
        If e.KeyCode = Keys.Escape Then
            If connection.State <> ConnectionState.Closed Then
                INSERE_INFO_LOG("CLOSING", "Fechando conexão com " & login_box.Text)
                connection.Close()
            End If
            enderecos_grade.Columns.Clear()
            nome_da_tabela_box.Items.Clear()
            nome_da_tabela_box.Enabled = True
            login_box.Enabled = True
            login_box.Focus()
        End If

        If e.Control AndAlso e.KeyCode = Keys.F Then googlesearch.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.G Then pesquisageo.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.K Then arrumaendereco.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.P Then PESQUISACEPBT.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.L Then latsearch.PerformClick()

        If e.Control AndAlso e.KeyCode = Keys.S Then
            For Each coluna As DataGridViewColumn In enderecos_grade.Columns
                Select Case nome_da_tabela_box.Text
                    Case "onde_encontrar_novo"
                        NEW_SALVALINHA(coluna.Name, "idOndeEncontrar")
                    Case Else
                        NEW_SALVALINHA(coluna.Name, "PON_ID")
                End Select

            Next
        End If

        If e.Control AndAlso e.KeyCode = Keys.I Then import_sql.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.T Then create_table.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.M Then googlemapsearch.PerformClick()
        If e.Control AndAlso e.KeyCode = Keys.D Then searchduplicate.PerformClick()
        If e.KeyCode = Keys.F7 Then cidenable.PerformClick()

        If e.Shift AndAlso e.KeyCode = Keys.Escape Then
            connection.Close()
            login_box.Focus()

        End If


        If e.KeyCode = Keys.F8 Or e.Control AndAlso e.KeyCode = Keys.Tab Then
            If abas.SelectedTab Is tabbrowser Then abas.SelectTab(tabgrade) Else abas.SelectTab(tabbrowser)
            sender.focus()
        End If

        If e.KeyCode = Keys.Delete And enderecos_grade.Focused = True Then
            For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
                celula.Value = ""
            Next
        End If

        If e.KeyCode = Keys.F2 Then
            nome_da_tabela_box.Enabled = True
            nome_da_tabela_box.Text = ""
            nome_da_tabela_box.Focus()
            ORDERBOX.Items.Clear()
            enderecos_grade.Columns.Clear()
        End If
        If e.KeyCode = Keys.F3 Then
            conditionbox.Focus()
            enderecos_grade.Columns.Clear()
        End If

    End Sub



    Private Sub arrumaendereco_Click(sender As Object, e As EventArgs) Handles arrumaendereco.Click
        selecionador(enderecos_grade)
        For Each linha In enderecos_grade.SelectedRows
            INSERE_INFO_LOG("BREAKING", "Quebrando Endereço: " & linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value)
            Try
                Dim elementos_Do_endereco = linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value.ToString.Split("-")
                linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value = elementos_Do_endereco.GetValue(0).ToString.Trim()
                linha.Cells.Item(pegarindexcoluna("PON_BAIRRO")).Value = elementos_Do_endereco.GetValue(1).ToString.Trim()
                linha.Cells.Item(pegarindexcoluna("PON_CIDADE")).Value = elementos_Do_endereco.GetValue(2).ToString.Trim()
                linha.Cells.Item(pegarindexcoluna("PON_ESTADO")).Value = elementos_Do_endereco.GetValue(3).ToString.Trim()
                linha.Cells.Item(pegarindexcoluna("PON_CEP")).Value = elementos_Do_endereco.GetValue(4).ToString.Trim() & "-" & elementos_Do_endereco.GetValue(5).ToString.Trim()
            Catch ex As Exception
            End Try
            INSERE_INFO_LOG("FIXED", "Endereço Fixado como: " & linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value)
        Next
    End Sub

    Sub preencheorderbox()
        Try
            Dim lastitem As String = ORDERBOX.Text
            ORDERBOX.Items.Clear()
            Dim leitor As SqlDataReader
            Dim comandobox As New SqlCommand("SELECT NAME FROM SYSCOLUMNS WHERE ID = OBJECT_ID('" & nome_da_tabela_box.Text & "')", connection)
            leitor = comandobox.ExecuteReader
            While leitor.Read
                ORDERBOX.Items.Add(leitor.Item("name"))
            End While
            leitor.Close()
            If String.IsNullOrWhiteSpace(lastitem) Then lastitem = ORDERBOX.Items.Item(0).ToString
            ORDERBOX.SelectedItem = lastitem
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try

    End Sub

    Private Sub find_latlong(sender As Object, e As EventArgs) Handles pesquisageo.Click
        selecionador(enderecos_grade)
        Dim vaid As String, vairua As String = "endereco", vaicidade As String = "cidade", vaibairro As String = "bairro", vaiestado As String = "estado", vailatitude As String = "latitude", vailongitude As String = "longitude"
        For Each linha As DataGridViewRow In enderecos_grade.SelectedRows
            Try
                Select Case nome_da_tabela_box.Text

                    Case "onde_encontrar_novo"
                        vaid = "idOndeEncontrar"
                        vairua = "endereco"
                        vaicidade = "cidade"
                        vaibairro = "bairro"
                        vaiestado = "estado"
                        vailatitude = "latitude"
                        vailongitude = "longitude"
                    Case Else
                        vaid = "pon_ID"
                        vairua = "PON_endereco"
                        vaicidade = "pon_cidade"
                        vaibairro = "pon_bairro"
                        vaiestado = "pon_estado"
                        vailatitude = "pon_latitude"
                        vailongitude = "pon_logitude"
                End Select
                Dim documento As New XmlDocument
                Dim urlgoogle = criaURL(linha, "latlong", vairua, vaicidade, vaibairro, vaiestado, vailatitude, vailongitude).ToString

                documento.Load(urlgoogle)
                If documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString = "OVER_QUERY_LIMIT" Then
                    INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "Você estourou o limite de busca diárias, continue novamente amanha! (Em caso de IP dinâmico, reinicie sua conexão com a internet e execute o programa novamente.)")
                    rolardatagrid(linha)
                    Exit For
                End If
                If documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString = "OK" Then
                    linha.Cells.Item(pegarindexcoluna(vailatitude)).Value = documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("result").SelectSingleNode("geometry").SelectSingleNode("location").ChildNodes(0).InnerText.Replace(".", ",")
                    linha.Cells.Item(pegarindexcoluna(vailongitude)).Value = documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("result").SelectSingleNode("geometry").SelectSingleNode("location").ChildNodes(1).InnerText.Replace(".", ",")
                    Try
                        dataAdapter.UpdateCommand = connection.CreateCommand()
                        Select Case nome_da_tabela_box.Text
                            Case "oNDEeNCONTRAR"
                                dataAdapter.UpdateCommand.CommandText = "UPDATE " & nome_da_tabela_box.Text & " SET PON_LATITUDE = convert(float,'" & linha.Cells("PON_LATITUDE").Value.ToString.Replace(",", ".") & "'), PON_LOGITUDE = convert(float,'" & linha.Cells("PON_LOGITUDE").Value.ToString.Replace(",", ".") & "') WHERE PON_ID = '" & linha.Cells("PON_ID").Value & "'"
                            Case Else
                                dataAdapter.UpdateCommand.CommandText = "UPDATE " & nome_da_tabela_box.Text & " SET PON_LATITUDE = convert(float,'" & linha.Cells("PON_LATITUDE").Value.ToString.Replace(",", ".") & "'), PON_LOGITUDE = convert(float,'" & linha.Cells("PON_LOGITUDE").Value.ToString.Replace(",", ".") & "') WHERE PON_ID = '" & linha.Cells("PON_ID").Value & "'"

                        End Select
                        INSERE_INFO_LOG("UPDATING", dataAdapter.UpdateCommand.CommandText)
                        If dataAdapter.UpdateCommand.ExecuteNonQuery() = 0 Then INSERE_INFO_LOG("ERROR", "Não foi possivel atualizar")
                        INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "ID: " & linha.Cells.Item(pegarindexcoluna(vaid)).Value.ToString & ", Endereço: " & linha.Cells.Item(pegarindexcoluna(vairua)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaibairro)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaicidade)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaiestado)).Value.ToString & " atualizado com sucesso!")
                        rolardatagrid(linha)
                    Catch ex As Exception
                        INSERE_ERRO_LOG(ex)
                    End Try
                Else
                    INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "Geolocalização não encontrada para: ID: " & linha.Cells.Item(pegarindexcoluna(vaid)).Value.ToString & ", Endereço: " & linha.Cells.Item(pegarindexcoluna(vairua)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaibairro)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaicidade)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(vaiestado)).Value.ToString)
                    rolardatagrid(linha)
                End If
            Catch ex As Exception
                INSERE_ERRO_LOG(ex)
                rolardatagrid(linha)
            End Try
        Next
    End Sub
    Sub startlisting()
        abas.SelectedTab = tabgrade
        nome_da_tabela_box.Text = nome_da_tabela_box.Text.ToUpper
        Try
            preencheorderbox()
            Dim PESQUISA As String
            If cidenable.Text = "CID_ID ON" Then
                If String.IsNullOrWhiteSpace(conditionbox.Text) Then
                    PESQUISA = "SELECT *, (SELECT CID_ID FROM CIDADE C WHERE EST_ID IN (SELECT EST_ID FROM Estado WHERE EST_SIGLA = T.PON_ESTADO) AND CID_NOME = T.PON_CIDADE) AS CID_ID FROM " & nome_da_tabela_box.Text & " T ORDER BY " & ORDERBOX.Text & " " & cimabaixo.Text
                Else
                    PESQUISA = "SELECT *, (SELECT CID_ID FROM CIDADE C WHERE EST_ID IN (SELECT EST_ID FROM Estado WHERE EST_SIGLA = T.PON_ESTADO) AND CID_NOME = T.PON_CIDADE) AS CID_ID FROM " & nome_da_tabela_box.Text & " T WHERE " & conditionbox.Text & " ORDER BY " & ORDERBOX.Text & " " & cimabaixo.Text
                End If
            Else
                If String.IsNullOrWhiteSpace(conditionbox.Text) Then
                    PESQUISA = "SELECT * FROM " & nome_da_tabela_box.Text & " ORDER BY " & ORDERBOX.Text & " " & cimabaixo.Text
                Else
                    PESQUISA = "SELECT * FROM " & nome_da_tabela_box.Text & " WHERE " & conditionbox.Text & " ORDER BY " & ORDERBOX.Text & " " & cimabaixo.Text
                End If
            End If
            If connection.State = ConnectionState.Closed Or connection.State = ConnectionState.Broken Then
                login_box.Focus()
            Else
                dataAdapter = New SqlDataAdapter(New SqlCommand(PESQUISA.ToUpper, connection))
                INSERE_INFO_LOG("LISTING", PESQUISA.ToUpper)
                Dim tabeladedados As New DataSet
                dataAdapter.Fill(tabeladedados, nome_da_tabela_box.Text)
                enderecos_grade.Columns.Clear()
                nome_da_tabela_box.Enabled = False
                enderecos_grade.DataSource = tabeladedados.Tables(0)
                Try
                    enderecos_grade.Columns(pegarindexcoluna("PON_ID")).ReadOnly = True
                    enderecos_grade.Columns(pegarindexcoluna("CID_ID")).ReadOnly = True
                    enderecos_grade.Columns("PON_ID").DisplayIndex = 0
                    enderecos_grade.Columns("CID_ID").DisplayIndex = 5
                Catch
                End Try
                conditionbox.Focus()
            End If
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
            nome_da_tabela_box.Enabled = True
            nome_da_tabela_box.Focus()
        End Try
    End Sub
    Private Sub nome_da_tabela_box_KeyDown(sender As Object, e As KeyEventArgs) Handles nome_da_tabela_box.KeyDown, conditionbox.KeyDown, ORDERBOX.KeyDown
        If e.KeyCode = Keys.Enter Then
            startlisting()
        End If
    End Sub


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        INSERE_INFO_LOG("WAITING", "Aguardando conexão...")
        login_box.Focus()
    End Sub

    Private Sub PESQUISACEPBT_Click(sender As Object, e As EventArgs) Handles PESQUISACEPBT.Click
        verificarcep(enderecos_grade)
    End Sub

    Private Sub ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles googlesearch.Click
        Dim pesquisarendereco As String = ""
        Try
            For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
                pesquisarendereco = "+" & pesquisarendereco & celula.Value.ToString & "+"
            Next
            navegador.Navigate("https://www.google.com.br/search?q=" & Trim(pesquisarendereco).Replace(" ", "+"))
            abas.SelectTab(tabbrowser)
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try

    End Sub

    Private Sub clicamouse(sender As Object, e As MouseEventArgs) Handles enderecos_grade.MouseClick
        Try
            If e.Button = Windows.Forms.MouseButtons.Right Then
                If enderecos_grade.CurrentCell.ColumnIndex = pegarindexcoluna("PON_ESTADO") Then menu_estado.Show(MousePosition) Else principalmenu.Show(MousePosition)
            End If
        Catch ex As Exception
        End Try

    End Sub

    Private Sub latsearch_Click(sender As Object, e As EventArgs) Handles latsearch.Click
        buscabairro(enderecos_grade)
    End Sub

    Private Sub ImportarDadosUSandoAssistenteDoSQLServerToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles import_sql.Click
        Try
            Process.Start("DTSWizard.exe")
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try

    End Sub

    Private Sub onlyerros_Click(sender As Object, e As EventArgs) Handles onlyerros.Click
        Try
            Dim arquivo As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(CurDir() & "\GEOLOCATION_ERROR.log", True)
            arquivo.WriteLine(vbNewLine & vbNewLine & vbNewLine & "==NOVA SEÇÃO @ " & Date.Now.ToString & " por " & Environment.UserName & "==")
            For Each linha As DataGridViewRow In error_list.Rows
                arquivo.WriteLine(linha.Cells("DATA").Value.ToString & " - " & linha.Cells("DESCRICAO").Value.ToString)
            Next
            arquivo.Close()
            Process.Start(CurDir() & "\GEOLOCATION_ERROR.log")
        Catch EX As Exception
            INSERE_ERRO_LOG(EX)
        End Try
    End Sub

    Private Sub create_table_Click(sender As Object, e As EventArgs) Handles create_table.Click
        Dim tabela = InputBox("Digite o nome da tabela:", "Criar Tabela", "", 100, 100)
        If String.IsNullOrWhiteSpace(tabela) = False Then
            tabela = tabela.Replace("teste_pontos_venda", "")
            Dim comando = "USE [" & login_box.Text & "]  SET ANSI_NULLS ON  SET QUOTED_IDENTIFIER ON  CREATE TABLE [dbo].[TESTE_PONTOS_VENDA_" & tabela & "]([PON_TITULO] [nvarchar](max) NULL,	[PON_ENDERECO] [nvarchar](max) NULL,	[PON_BAIRRO] [nvarchar](max) NULL,	[PON_CIDADE] [nvarchar](max) NULL,	[PON_ESTADO] [nvarchar](max) NULL,	[PON_TELEFONE] [nvarchar](max) NULL,	[PON_LATITUDE] [nvarchar](max) NULL,	[PON_LOGITUDE] [nvarchar](max) NULL, 	[PON_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, 	[PON_CEP] [nchar](10) NULL, [PON_CONTATO] [nvarchar](max) NULL,  CONSTRAINT [PK_TESTE_PONTOS_VENDA_" & tabela & "] PRIMARY KEY CLUSTERED (	[PON_ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] "
            dataAdapter = New SqlDataAdapter(New SqlCommand(comando, connection))
            dataAdapter.UpdateCommand = connection.CreateCommand()
            dataAdapter.UpdateCommand.CommandText = comando.ToUpper
            Try
                dataAdapter.UpdateCommand.ExecuteScalar()
                INSERE_INFO_LOG("CREATE", comando.ToUpper)
                preecnhecombobox()
            Catch ex As Exception
                INSERE_ERRO_LOG(ex)
            End Try
        End If
    End Sub


    Private Sub ApagarRegistroToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ApagarRegistroToolStripMenuItem.Click
        Dim NOMEDACHAVE
        Dim nomecampo
        Select Case nome_da_tabela_box.Text
            Case "onde_encontrar_novo"
                NOMEDACHAVE = "idOndeEncontrar"
                nomecampo = "NomeFantasia"
            Case Else
                NOMEDACHAVE = "PON_ID"
                nomecampo = "pon_titulo"
        End Select
        selecionador(enderecos_grade)
        For Each linha As DataGridViewRow In enderecos_grade.SelectedRows
            If MsgBox("Deseja realmente apagar " & linha.Cells(pegarindexcoluna(nomecampo)).value & "?", MsgBoxStyle.Exclamation + MsgBoxStyle.YesNo, Me.Text) = MsgBoxResult.Yes Then
                Try
                    dataAdapter.UpdateCommand = connection.CreateCommand()
                    Dim comando As String = "DELETE FROM " & nome_da_tabela_box.Text & " WHERE " & NOMEDACHAVE & " = " & linha.Cells(pegarindexcoluna(NOMEDACHAVE)).value
                    dataAdapter.UpdateCommand.CommandText = comando
                    dataAdapter.UpdateCommand.ExecuteNonQuery()
                    INSERE_INFO_LOG("DELETED", "REGISTRO DE ID: " & linha.Cells(pegarindexcoluna(NOMEDACHAVE)).value & " APAGADO")
                    enderecos_grade.Rows.Remove(linha)
                Catch ex As Exception
                    INSERE_ERRO_LOG(ex)
                End Try
            End If
        Next
    End Sub


    Private Sub googlemapsearch_Click(sender As Object, e As EventArgs) Handles googlemapsearch.Click
        Dim pesquisarendereco As String = ""
        Try
            For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
                pesquisarendereco = "+" & pesquisarendereco & celula.Value.ToString.Replace(",", ".") & "+"
            Next
            navegador.Navigate("https://www.google.com.br/maps/search/" & Trim(pesquisarendereco).Replace(" ", "+"))
            abas.SelectTab(tabbrowser)
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try

    End Sub

    Private Sub searchduplicate_Click(sender As Object, e As EventArgs) Handles searchduplicate.Click
        Try
            Dim PESQUISA As String
            PESQUISA = "SELECT DISTINCT * FROM " & nome_da_tabela_box.Text & " WHERE PON_ENDERECO In (SELECT PON_ENDERECO FROM " & nome_da_tabela_box.Text & " As PSEUDONIMO GROUP BY PON_ENDERECO HAVING Count(*) > 1 ) and PON_ATIVO = 'true' ORDER BY PON_ENDERECO "
            dataAdapter = New SqlDataAdapter(New SqlCommand(PESQUISA, connection))
            Dim tabeladedados As New DataSet
            dataAdapter.Fill(tabeladedados, nome_da_tabela_box.Text)
            enderecos_grade.Columns.Clear()
            enderecos_grade.DataSource = tabeladedados.Tables(0)
            enderecos_grade.Columns(pegarindexcoluna("PON_ID")).ReadOnly = True
            enderecos_grade.Focus()
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
            nome_da_tabela_box.Focus()
        End Try
    End Sub

    Private Sub error_list_RowsAdded(sender As Object, e As DataGridViewRowsAddedEventArgs) Handles error_list.RowsAdded
        Try
            error_list.FirstDisplayedScrollingRowIndex = error_list.FirstDisplayedScrollingRowIndex + e.RowCount
        Catch ex As Exception
        End Try

    End Sub

    Private Sub enderecos_grade_ColumnAdded(sender As Object, e As DataGridViewColumnEventArgs)
        If sender.columns.count > 1 Then procedimento_menu.Enabled = True Else procedimento_menu.Enabled = False
    End Sub

    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles cidenable.Click
        If sender.text = "CID_ID ON" Then sender.text = "CID_ID OFF" Else sender.text = "CID_ID ON"
    End Sub

    Private Sub clica_estado(sender As Object, e As EventArgs) Handles ACToolStripMenuItem.Click,
     ALToolStripMenuItem.Click,
     APAmapáToolStripMenuItem.Click,
     AMAmazonasToolStripMenuItem.Click,
     BABahiaToolStripMenuItem.Click,
     CECearáToolStripMenuItem.Click,
     DFDistritoFederalToolStripMenuItem.Click,
     ESEspiritoSantoToolStripMenuItem.Click,
     GOGoiásToolStripMenuItem.Click,
     MAMaranhãoToolStripMenuItem.Click,
     MTMatoGrossoToolStripMenuItem.Click,
     MSMatoGrossoDoSUlToolStripMenuItem.Click,
     MGMinasGeraisToolStripMenuItem.Click,
     PAParáToolStripMenuItem.Click,
     PBJoãoPEssoaToolStripMenuItem.Click,
     PRParanáToolStripMenuItem.Click,
     PEPernambucoToolStripMenuItem.Click,
     PIPiauiToolStripMenuItem.Click,
     RJRioDeJaneiroToolStripMenuItem.Click,
     RNRioGrandeDoNorteToolStripMenuItem.Click,
     RSRioGrandeDoSulToolStripMenuItem.Click,
     RORondôniaToolStripMenuItem.Click,
     RRRoraimaToolStripMenuItem.Click,
     SCSantaCatarinaToolStripMenuItem.Click,
     SPSãoPauloToolStripMenuItem.Click,
     SESergipeToolStripMenuItem.Click,
     TOTocantinsToolStripMenuItem.Click

        For Each celula As DataGridViewCell In enderecos_grade.SelectedCells

            Dim estado() As String = Split(sender.text, " ")
            celula.Value = estado(0).ToString.ToUpper
            celula.OwningRow.Selected = True
            Try
                NEW_SALVALINHA("PON_ESTADO", "PON_ID")
            Catch ex As Exception
                INSERE_ERRO_LOG(ex)
            End Try

        Next

    End Sub

    Private Sub ToolStripMenuItem1_Click_1(sender As Object, e As EventArgs) Handles colar.Click
        For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
            If (Clipboard.GetDataObject().GetDataPresent(DataFormats.Text)) Then
                celula.Value = Clipboard.GetDataObject().GetData(DataFormats.Text).ToString().ToUpper
            Else
                INSERE_INFO_LOG("NO_CLIPBOARD", "Não há texto na área de transferência.")
            End If
        Next
    End Sub

    Private Sub LIMPADACLIP_Click(sender As Object, e As EventArgs) Handles LIMPADACLIP.Click
        For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
            If (Clipboard.GetDataObject().GetDataPresent(DataFormats.Text)) Then
                celula.Value = celula.Value.replace(Clipboard.GetDataObject().GetData(DataFormats.Text).ToString(), "")
            Else
                INSERE_INFO_LOG("NO_CLIPBOARD", "Não há texto na área de transferência.")
            End If
        Next
    End Sub


    Sub abrirconexao(endereco_ip As String, login As String, senha As String, nomebanco As String)
        Try
            connection = New SqlConnection("Data Source=" & endereco_ip & ";Initial Catalog=" & nomebanco & ";User ID=" & login & ";Password=" & senha & ";")
            INSERE_INFO_LOG("CONNECTING", "Por favor aguarde, estamos conectando com " & nomebanco.ToUpper & " @ " & endereco_ip & "...")
            connection.Open()
            Me.Update()
            If connection.State = ConnectionState.Open Then INSERE_INFO_LOG("CONNECTED", "Conectado com " & nomebanco.ToUpper & " @ " & endereco_ip & "...")
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try
    End Sub
    Sub preecnhecombobox()
        Try
            nome_da_tabela_box.Items.Clear()
            Dim leitor As SqlDataReader
            Dim comandobox As New SqlCommand("SELECT * FROM SYSOBJECTS WHERE XTYPE='U' and (name like '%TESTE_PONTOS_VENDA_%' or name = 'pontosvenda' or name = 'onde_encontrar_novo' or name = 'naolocalizados')", connection)
            leitor = comandobox.ExecuteReader
            While leitor.Read
                nome_da_tabela_box.Items.Add(leitor.Item("name"))
            End While
            leitor.Close()
        Catch ex As Exception
            INSERE_ERRO_LOG(ex)
        End Try

    End Sub

    Private Sub conectaragora(sender As Object, e As EventArgs) Handles login_box.SelectedIndexChanged
        Dim senha As String
        If login_box.Text = "NATONE" Then senha = "locaweb1020" Else senha = "d3m7k8"
        abrirconexao("186.202.124.13", login_box.Text, senha, login_box.Text)
        preecnhecombobox()
        nome_da_tabela_box.Enabled = True
        nome_da_tabela_box.Focus()
        login_box.Enabled = False
    End Sub

    Private Sub ToolStripTextBox1_Click(sender As Object, e As EventArgs) Handles findthis.TextChanged
        navegador.Navigate("https://www.google.com.br/search?q=" & sender.text.replace(" ", "+"))
    End Sub

    Private Sub ToolStripButton1_Click_1(sender As Object, e As EventArgs) Handles VOLTANAV.Click
        If navegador.CanGoBack Then navegador.GoBack()
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles VAINAV.Click
        If navegador.CanGoForward Then navegador.GoForward()
    End Sub

    Private Sub TrocarPalavraDaCLipboarToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles TrocarPalavraDaCLipboarToolStripMenuItem.Click
        For Each celula As DataGridViewCell In enderecos_grade.SelectedCells
            If (Clipboard.GetDataObject().GetDataPresent(DataFormats.Text)) Then
                If Not String.IsNullOrWhiteSpace(textodetroca.Text) Then
                    celula.Value = celula.Value.replace(Clipboard.GetDataObject().GetData(DataFormats.Text).ToString(), textodetroca.Text.ToUpper)
                Else
                    INSERE_INFO_LOG("NO_REPLACE_TEXT", "Não há texto na caixa de troca.")
                End If
            Else
                INSERE_INFO_LOG("NO_CLIPBOARD", "Não há texto na área de transferência.")
            End If
        Next
    End Sub


    Private Sub nome_da_tabela_box_SelectedIndexChanged(sender As Object, e As EventArgs) Handles nome_da_tabela_box.SelectedIndexChanged
        startlisting()
    End Sub

    Private Sub ToolStripButton1_Click_2(sender As Object, e As EventArgs) Handles cimabaixo.Click
        If sender.text = "ASC" Then sender.text = "DESC" Else sender.text = "ASC"
    End Sub


    Private Sub findgrid(sender As Object, e As KeyEventArgs) Handles pesquisadentro.KeyDown
        If e.Shift And e.KeyCode = Keys.Enter Then pesquisadentro.Text = pesquisadentro.Text.ToUpper
        If e.KeyCode = Keys.Enter And String.IsNullOrWhiteSpace(pesquisadentro.Text) = False Then
            Try
                For Each linha As DataGridViewRow In enderecos_grade.Rows
                    For Each celula As DataGridViewCell In linha.Cells
                        Dim valores() = Split(celula.Value.ToString.ToUpper, " ")
                        For Each valor As String In valores
                            If valor = pesquisadentro.Text Then
                                enderecos_grade.CurrentCell = celula
                                Exit Sub
                            End If

                        Next
                    Next
                Next
            Catch ex As Exception
            End Try
        End If
    End Sub

    Private Sub GOTOADM_Click(sender As Object, e As EventArgs) Handles GOTOADM.Click
        navegador.Navigate("http://natone.com.br/administracao/interna.asp")
    End Sub

    Private Sub gotosite_Click(sender As Object, e As EventArgs) Handles gotosite.Click
        If login_box.Text = "NATONE" Then navegador.Navigate("http://natone.com.br/home/onde-comprar") Else navegador.Navigate("http://www.caipi-one.com/where-to-buy")
    End Sub

    Private Sub enderecos_grade_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles enderecos_grade.CellEndEdit
        If nome_da_tabela_box.Text = "PontosVenda" And enderecos_grade.CurrentCell.ColumnIndex = pegarindexcoluna("PON_ATIVO") Then NEW_SALVALINHA("pon_ativo", "pon_id")
    End Sub

End Class



Module geolocalizador
    'variaveis globais
    Public verifica As Boolean = False
    Public Property ScriptErrorsSuppressed As Boolean = True
    Dim cidade As String = String.Empty
    Dim estado As String = String.Empty
    Dim rua As String = String.Empty
    Dim bairro As String = String.Empty
    Dim latitude As String = String.Empty
    Dim longitude As String = String.Empty
    Dim adapter As New SqlDataAdapter
    Dim LATLONG As String = String.Empty
    Dim MEU_DATASET As DataSet = New DataSet()


    Function tratar(ByVal palavra As String)

        palavra = palavra.Replace("ã", "a")
        palavra = palavra.Replace("á", "a")
        palavra = palavra.Replace("à", "a")
        palavra = palavra.Replace("â", "a")
        palavra = palavra.Replace("ê", "e")
        palavra = palavra.Replace("é", "e")
        palavra = palavra.Replace("è", "e")
        palavra = palavra.Replace("í", "i")
        palavra = palavra.Replace("ì", "i")
        palavra = palavra.Replace("î", "i")
        palavra = palavra.Replace("ó", "o")
        palavra = palavra.Replace("ò", "o")
        palavra = palavra.Replace("ô", "o")
        palavra = palavra.Replace("õ", "o")
        palavra = palavra.Replace("ú", "u")
        palavra = palavra.Replace("ù", "u")
        palavra = palavra.Replace("û", "u")
        palavra = palavra.Replace("ç", "c")
        palavra = palavra.Replace(" ", "+")
        Return palavra
    End Function

    Sub rolardatagrid(linha As DataGridViewRow)
        Try
            Form1.enderecos_grade.FirstDisplayedScrollingRowIndex() = linha.Index - 10
        Catch ex As Exception
            Form1.enderecos_grade.FirstDisplayedScrollingRowIndex() = linha.Index
        End Try
    End Sub



    Sub verificarcep(gradepesquisa As DataGridView)
        If gradepesquisa.SelectedRows.Count = 0 Then
            If gradepesquisa.SelectedCells.Count >= 1 Then
                gradepesquisa.CurrentRow.Selected = True
            End If
        End If
        For Each linha As DataGridViewRow In gradepesquisa.SelectedRows
            Try
                Dim consultaEndereco As New StringBuilder
                consultaEndereco.Append("http://apps.widenet.com.br/busca-cep/api/cep/" & linha.Cells.Item(pegarindexcoluna("PON_CEP")).Value.Replace("-", "") & ".xml")
                Dim documento As New XmlDocument
                documento.Load(consultaEndereco.ToString)
                If documento.SelectSingleNode("cep").SelectSingleNode("status").InnerText.ToString = "1" Then
                    If String.IsNullOrEmpty(linha.Cells.Item(pegarindexcoluna("PON_bairro")).Value.ToString) = True Then linha.Cells.Item(pegarindexcoluna("PON_BAIRRO")).Value = documento.SelectSingleNode("cep").SelectSingleNode("district").InnerText.ToString.ToUpper
                    If String.IsNullOrEmpty(linha.Cells.Item(pegarindexcoluna("PON_cidade")).Value.ToString) = True Then linha.Cells.Item(pegarindexcoluna("PON_CIDADE")).Value = documento.SelectSingleNode("cep").SelectSingleNode("city").InnerText.ToString.ToUpper
                    If String.IsNullOrEmpty(linha.Cells.Item(pegarindexcoluna("PON_estado")).Value.ToString) = True Then linha.Cells.Item(pegarindexcoluna("PON_ESTADO")).Value = documento.SelectSingleNode("cep").SelectSingleNode("state").InnerText.ToString.ToUpper
                    If String.IsNullOrEmpty(linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value.ToString) = True Then linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value = documento.SelectSingleNode("cep").SelectSingleNode("address").InnerText.ToString
                Else
                    INSERE_INFO_LOG(documento.SelectSingleNode("cep").SelectSingleNode("status").InnerText.ToString, "Localização por CEP não encontrado para ID: " & linha.Cells.Item(pegarindexcoluna("PON_ID")).Value.ToString & ", Nome: " & linha.Cells.Item(pegarindexcoluna("PON_TITULO")).Value.ToString & " Endereço: " & linha.Cells.Item(pegarindexcoluna("PON_ENDERECO")).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna("PON_BAIRRO")).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna("PON_CIDADE")).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna("PON_ESTADO")).Value.ToString)
                    rolardatagrid(linha)
                End If
            Catch EX As Exception
                INSERE_ERRO_LOG(EX)
                rolardatagrid(linha)
            End Try
        Next


    End Sub
    Sub INSERE_ERRO_LOG(EX As Exception)
        Try
            If Form1.connection.State = ConnectionState.Closed Or Form1.connection.State = ConnectionState.Broken Then
                INSERE_INFO_LOG("NO_CONNECTION", "Conexão com o banco de dados caiu.")
                Form1.login_box.Focus()

            End If
        Catch
        End Try
        Form1.error_list.Rows.Add({"ERROR", Date.Now.ToString, Environment.UserName, EX.Source & " - " & EX.Message})
        Form1.Update()
    End Sub
    Sub INSERE_INFO_LOG(status As String, texto As String)
        Form1.error_list.Rows.Add({status.ToUpper, Date.Now.ToString, Environment.UserName, texto})
        Form1.Update()
    End Sub

    Public Function pegarindexcoluna(nome As String)
        Dim indexcoluna As Integer = Form1.enderecos_grade.Columns.IndexOf(Form1.enderecos_grade.Columns.Item(nome))
        Return indexcoluna
    End Function

    Function contatracos(frase As String)
        Dim quant As Integer = 0
        Dim pos As Integer = -1, pos_ant As Integer = -1
        Dim cont As Integer = 0
        While cont < frase.Length
            pos = frase.IndexOf("-", cont)
            If pos <> pos_ant And pos <> -1 Then
                quant += 1
            End If
            cont += 1
            pos_ant = pos
        End While
        Return quant
    End Function

    Function criaURL(linha As DataGridViewRow, objetivo As String, u_rua As String, u_cidade As String, u_bairro As String, u_estado As String, u_latitude As String, u_longitude As String) As String

        Dim consultaEndereco As New StringBuilder
        consultaEndereco.Append("http://maps.googleapis.com/maps/api/geocode/xml?address=")
        Select Case objetivo
            Case "latlong"
                ' constroi a consulta pelo nome da rua
                rua = linha.Cells.Item(pegarindexcoluna(u_rua)).Value.ToString.Replace(" ", "+")
                consultaEndereco.Append(rua & "+")
                ' constroi a consulta pelo nome da cidade
                cidade = linha.Cells.Item(pegarindexcoluna(u_cidade)).Value.ToString.Replace(" ", "+")
                consultaEndereco.Append(cidade & "+")
                ' constroi a consulta pelo nome do estado
                If Form1.nome_da_tabela_box.Text <> "pontosvenda" Then
                    estado = linha.Cells.Item(pegarindexcoluna(u_estado)).Value.ToString.Replace(" ", "+")
                    consultaEndereco.Append(estado & "+")
                End If
                ' constroi a consulta pelo bairro
                bairro = linha.Cells.Item(pegarindexcoluna(u_bairro)).Value.ToString.Replace(" ", "+")
                consultaEndereco.Append(bairro & "+")
            Case "bairro"
                LATLONG = linha.Cells.Item(pegarindexcoluna(u_latitude)).Value.ToString.Replace(" ", "+") & "," & linha.Cells.Item(pegarindexcoluna(u_longitude)).Value.ToString.Replace(" ", "+")
                consultaEndereco.Append(LATLONG)
        End Select
        consultaEndereco.Append("&sensor=true")
        INSERE_INFO_LOG("URL", consultaEndereco.ToString)
        Return consultaEndereco.ToString
    End Function

    Sub buscabairro(grade As DataGridView)
        selecionador(grade)
        Dim u_id As String, u_rua As String = "endereco", u_cidade As String = "cidade", u_bairro As String = "bairro", u_estado As String = "estado", u_latitude As String = "latitude", u_longitude As String = "longitude"

        For Each linha As DataGridViewRow In grade.SelectedRows
            Select Case Form1.nome_da_tabela_box.Text
                Case "PontosVenda"
                    u_id = "pon_id"
                    u_rua = "PON_endereco"
                    u_cidade = "pon_cidade"
                    u_bairro = "pon_bairro"
                    u_estado = "pon_estado"
                    u_latitude = "pon_latitude"
                    u_longitude = "pon_logitude"
                Case "onde_encontrar_novo"
                    u_id = "idOndeEncontrar"
                    u_rua = "endereco"
                    u_cidade = "cidade"
                    u_bairro = "bairro"
                    u_estado = "estado"
                    u_latitude = "latitude"
                    u_longitude = "longitude"
                Case Else
                    u_id = "PON_ID"
                    u_rua = "PON_endereco"
                    u_cidade = "PON_cidade"
                    u_bairro = "PON_bairro"
                    u_estado = "PON_estado"
                    u_latitude = "PON_latitude"
                    u_longitude = "PON_logitude"
            End Select
            Try
                Dim documento As New XmlDocument
                Dim urlgoogle = criaURL(linha, "bairro", u_rua, u_cidade, u_bairro, u_estado, u_latitude, u_longitude).ToString()
                documento.Load(urlgoogle)
                If documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString = "OVER_QUERY_LIMIT" Then
                    INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "Você estourou o limite de busca diárias, continue novamente amanha! (Em caso de IP dinâmico, reinicie sua conexão com a internet e execute o programa novamente.)")
                    rolardatagrid(linha)
                    Exit For
                End If
                If documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString = "OK" And documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("result").ChildNodes(4).SelectSingleNode("type").InnerText = "neighborhood" Then
                    bairro = documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("result").ChildNodes(4).SelectSingleNode("long_name").InnerText
                    If String.IsNullOrWhiteSpace(linha.Cells.Item(pegarindexcoluna(u_bairro)).Value.ToString) = True Then linha.Cells.Item(pegarindexcoluna(u_bairro)).Value = bairro
                    INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "Bairro atualizado -  ID: " & linha.Cells.Item(pegarindexcoluna(u_id)).Value.ToString & ", Endereço: " & linha.Cells.Item(pegarindexcoluna(u_rua)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_bairro)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_cidade)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_estado)).Value.ToString & " atualizado com sucesso!")
                    rolardatagrid(linha)
                Else
                    INSERE_INFO_LOG(documento.SelectSingleNode("GeocodeResponse").SelectSingleNode("status").InnerText.ToString, "Bairro não encontrado para: ID: " & linha.Cells.Item(pegarindexcoluna(u_id)).Value.ToString & ", Endereço: " & linha.Cells.Item(pegarindexcoluna(u_rua)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_bairro)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_cidade)).Value.ToString & ", " & linha.Cells.Item(pegarindexcoluna(u_estado)).Value.ToString & " atualizado com sucesso!")
                    rolardatagrid(linha)
                End If
                cidade = String.Empty
                estado = String.Empty
                bairro = String.Empty
                latitude = String.Empty
                rua = String.Empty
                longitude = String.Empty
            Catch ex As Exception
                INSERE_ERRO_LOG(ex)
                rolardatagrid(linha)
            End Try
        Next
    End Sub

    Sub selecionador(grade As DataGridView)
        If grade.SelectedRows.Count = 0 Then
            If grade.SelectedCells.Count >= 1 Then
                For Each celula In grade.SelectedCells
                    celula.OwningRow.Selected = True
                Next
            End If
        End If
    End Sub

    Sub fixaendereco(linha As DataGridViewRow)
        selecionador(Form1.enderecos_grade)

        If linha.Cells(pegarindexcoluna("PON_ENDERECO")).value.ToString.StartsWith("R ") Then
            Dim texto() As String = linha.Cells(pegarindexcoluna("PON_ENDERECO")).value.ToString.Split(" ", 2, StringSplitOptions.RemoveEmptyEntries)
            linha.Cells(pegarindexcoluna("PON_ENDERECO")).value = "RUA " & texto(1)
        End If
        linha.Cells(pegarindexcoluna("PON_ENDERECO")).value = linha.Cells(pegarindexcoluna("PON_ENDERECO")).value.ToString.Replace(" ,", ",").Replace(",", ", ")
        linha.Cells(pegarindexcoluna("PON_titulo")).value = linha.Cells(pegarindexcoluna("PON_titulo")).value.ToString.Replace(" ,", ",").Replace(",", ", ")
    End Sub





    Function limpaespacos(texto As String)
        Try
            texto = texto.Replace(";", ",")
            texto = texto.Replace(":", " ")
            texto = texto.Replace(",,", ",")
            texto = texto.Replace("JD ", "JARDIM ")
            texto = texto.Replace("PQ ", "PARQUE ")
            texto = texto.Replace("PCA ", "PRAÇA ")
            texto = texto.Replace("PÇA", "PRAÇA ")
            texto = texto.Replace("COHAB ", "CONJUNTO HABITACIONAL ")
            texto = texto.Replace("CDHU ", "Companhia de Desenvolvimento Habitacional e Urbano ".ToUpper)
            texto = texto.Replace("VL ", "VILA ")
            texto = texto.Replace("ROD ", "RODOVIA ")
            texto = texto.Replace("AV ", "AVENIDA ")
            texto = texto.Replace("AVN ", "AVENIDA ")
            texto = texto.Replace("DIST ", "DISTRITO ")
            texto = texto.Replace("CJ ", "CONJUNTO ")
            texto = texto.Replace("DR ", "DOUTOR ")
            texto = texto.Replace("LOT ", "LOTEAMENTO ")
            texto = texto.Replace(" STA ", " SANTA ")
            texto = texto.Replace(" STO ", " SANTO ")
            For i = 0 To texto.Length()
                texto = Replace(texto, "  ", " ")
            Next
            texto = texto.Trim
            Return texto.ToUpper
        Catch
            texto = ""
            Return texto.ToUpper
        End Try
    End Function

    Sub NEW_SALVALINHA(NOMEDOCAMPO As String, NOMEDACHAVE As String)
        selecionador(Form1.enderecos_grade)

        For Each LINHA As DataGridViewRow In Form1.enderecos_grade.SelectedRows
            If Form1.login_box.Text = "NATONE" Then fixaendereco(LINHA)
            Form1.dataAdapter.UpdateCommand = Form1.connection.CreateCommand()
            If NOMEDOCAMPO.ToUpper <> NOMEDACHAVE.ToUpper Then
                Dim campo = LINHA.Cells(NOMEDOCAMPO).Value.ToString
                Select Case NOMEDOCAMPO.ToUpper
                    Case "PON_LATITUDE", "PON_LOGITUDE", "LONGITUDE", "LATITUDE"
                        Form1.dataAdapter.UpdateCommand.CommandText = "UPDATE " & Form1.nome_da_tabela_box.Text & " SET " & NOMEDOCAMPO & " = convert(float,'" & campo.Replace(",", ".") & "') WHERE " & NOMEDACHAVE & " = '" & LINHA.Cells(NOMEDACHAVE).Value & "'"
                    Case Else
                        Form1.dataAdapter.UpdateCommand.CommandText = "UPDATE " & Form1.nome_da_tabela_box.Text & " SET " & NOMEDOCAMPO & " = '" & limpaespacos(campo) & "' WHERE " & NOMEDACHAVE & " = '" & LINHA.Cells(NOMEDACHAVE).Value & "'"
                End Select
                INSERE_INFO_LOG("UPDATING", Form1.dataAdapter.UpdateCommand.CommandText)
                Try
                    Form1.dataAdapter.UpdateCommand.ExecuteNonQuery()
                Catch ex As Exception
                    INSERE_ERRO_LOG(ex)
                End Try
            Else
                INSERE_INFO_LOG("PRIMARY KEY FOUND", "Você não pode atualizar uma chave primária...")
                Exit For
            End If
        Next

    End Sub
End Module
